unit QImport3Xlsx;

{$I QImport3VerCtrl.Inc}

interface

{$IFDEF XLSX}
{$IFDEF VCL6}

uses
  QImport3StrTypes, QImport3, QImport3Common, QImport3BaseDocumentFile, Classes,
  SysUtils, IniFiles, msxml;

type
  TXlsxStyle = class(TCollectionItem)
  private
    FNumFmtId: Integer;
  public
    property NumFmtId: Integer read FNumFmtId write FNumFmtId;
  end;

  TXlsxStyleList = class(TCollection)
  private
    function GetItem(Index: Integer): TXlsxStyle;
    procedure SetItem(Index: Integer; const Value: TXlsxStyle);
  public
    function Add: TXlsxStyle;
    property Items[Index: Integer]: TXlsxStyle read GetItem write SetItem; default;
  end;

  TXlsxSharedStrings = class(TCollectionItem)
  private
    FText: qiString;
  public
    property Text: qiString read FText write FText;
  end;

  TXlsxSharedStringList = class(TCollection)
  private
    function GetItem(Index: Integer): TXlsxSharedStrings;
    procedure SetItem(Index: Integer; const Value: TXlsxSharedStrings);
  public
    function Add: TXlsxSharedStrings;
    property Items[Index: Integer]: TXlsxSharedStrings read GetItem write SetItem; default;
  end;

  TXlsxMerge = class(TCollectionItem)
  private
//    FRange: string;
    FRange: qiString;
    FValue: qiString;
    FBeginRow: Integer;
    FBeginCol: Integer;
    FEndRow: Integer;
    FEndCol: Integer;
//    FFirstCellName: string;
    FFirstCellName: qiString;
//    procedure SetRange(const Value: string);
    procedure SetRange(const Value: qiString);
  public
//    property Range: string read FRange write SetRange;
    property Range: qiString read FRange write SetRange;
    property Value: qiString read FValue write FValue;
//    property FirstCellName: AnsiString read FFirstCellName;
    property FirstCellName: qiString read FFirstCellName;
    property BeginRow: Integer read FBeginRow;
    property BeginCol: Integer read FBeginCol;
    property EndRow: Integer read FEndRow;
    property EndCol: Integer read FEndCol;
  end;

  TXlsxMergeList = class(TCollection)
  private
    function GetItem(Index: Integer): TXlsxMerge;
    procedure SetItem(Index: Integer; const Value: TXlsxMerge);
  public
    function Add: TXlsxMerge;
    property Items[Index: Integer]: TXlsxMerge read GetItem write SetItem; default;
  end;

  TXlsxCell = class(TCollectionItem)
  private
    FName: string;
    FRow: Integer;
    FCol: Integer;
    FValue: qiString;
    FFormula: string;
    FIsFormulaExist: Boolean;
    FIsMerge: Boolean;
    procedure SetFormula(const Value: string);
    function GetName: string;
    procedure SetName(const Value: string);
  public
    constructor Create(Collection: TCollection); override;

    property Name: string read GetName write SetName;
    property Value: qiString read FValue write FValue;
    property Row: Integer read FRow write FRow;
    property Col: Integer read FCol write FCol;
    property IsMerge: Boolean read FIsMerge write FIsMerge;
    property Formula: string read FFormula write SetFormula;
    property IsFormulaExist: Boolean read FIsFormulaExist;
  end;

  TXlsxCellList = class(TCollection)
  private
    function GetItem(Index: Integer): TXlsxCell;
    procedure SetItem(Index: Integer; const Value: TXlsxCell);
  public
    function Add: TXlsxCell;
    property Items[Index: Integer]: TXlsxCell read GetItem write SetItem; default;
  end;

  TXlsxWorkSheet = class(TCollectionItem)
  private
    FName: string;
    FSheetID: integer;
    FColCount: Integer;
    FRowCount: Integer;
    FCells: TXlsxCellList;
    FDataCells: TqiStringGrid;
    FMergeCells: TXlsxMergeList;
    FIsHidden: boolean;
    procedure SetSheetID(const Value: integer);
  public
    constructor Create(Collection: TCollection); override;
    destructor Destroy; override;

    procedure FillMerge(Cell: TXlsxCell);
    procedure LoadDataCells;
    property Name: string read FName write FName;
    property SheetID: integer read FSheetID write SetSheetID;
    property ColCount: Integer read FColCount write FColCount;
    property RowCount: Integer read FRowCount write FRowCount;
    property Cells: TXlsxCellList read FCells;
    property DataCells: TqiStringGrid read FDataCells;
    property MergeCells: TXlsxMergeList read FMergeCells;
    property IsHidden: boolean read FIsHidden write FIsHidden;
  end;

  TXlsxWorkSheetList = class(TCollection)
  private
    function GetItems(Index: integer): TXlsxWorkSheet;
    procedure SetItems(Index: integer; Value: TXlsxWorkSheet);
  public
    function Add: TXlsxWorkSheet;
    property Items[Index: integer]: TXlsxWorkSheet read GetItems
      write SetItems; default;

    function GetFirstSheet: TXlsxWorkSheet;
    function GetSheetByName(Name: qiString): TXlsxWorkSheet;
    function GetSheetByID(id: integer): TXlsxWorkSheet;
  end;

  TXlsxWorkbook = class
  private
    FWorkDir: string;
    FXMLDoc: IXMLDOMDocument;
    FWorkSheets: TXlsxWorkSheetList;
    FSharedStrings: TXlsxSharedStringList;
    FStyles: TXlsxStyleList;
    FLoadHiddenSheets: Boolean;
    FNeedFillMerge: Boolean;
    procedure LoadSharedStrings;
    procedure LoadStyles;
    procedure SetWorkSheets;
    procedure LoadWorkSheets;
    procedure SetDataCells;
    procedure LoadSheet(SheetFile: qiString; Id: integer);
  private
    property Styles: TXlsxStyleList read FStyles;
  public
    constructor Create;
    destructor Destroy; override;

    procedure Load;
    property SharedStrings: TXlsxSharedStringList read FSharedStrings;
    property CurrFolder: string read FWorkDir write FWorkDir;
    property WorkSheets: TXlsxWorkSheetList read FWorkSheets;
    property LoadHiddenSheets: Boolean read FLoadHiddenSheets write FLoadHiddenSheets;
    property NeedFillMerge: Boolean read FNeedFillMerge write FNeedFillMerge;
  end;

  TXlsxFile = class(TBaseDocumentFile)
  private
    FWorkbook: TXlsxWorkbook;
  protected
    procedure LoadXML(CurrFolder: qiString); override;
  public
    constructor Create; override;
    destructor Destroy; override;
    property Workbook: TXlsxWorkbook read FWorkbook;
  end;

  TQImport3Xlsx = class(TQImport3)
  private
    FXlsxFile: TXlsxFile;
    FCounter: Integer;
    FSheetName: string;
    FLoadHiddenSheet: Boolean;
    FNeedFillMerge: Boolean;
    procedure SetSheetName(const Value: string);
    procedure SetLoadHiddenSheet(const Value: Boolean);
    procedure SetNeedFillMerge(const Value: Boolean);
  protected
    procedure BeforeImport; override;
    procedure StartImport; override;
    function CheckCondition: Boolean; override;
    function Skip: Boolean; override;
    procedure ChangeCondition; override;
    procedure FinishImport; override;
    procedure AfterImport; override;
    procedure FillImportRow; override;
    function ImportData: TQImportResult; override;
    procedure DoLoadConfiguration(IniFile: TIniFile); override;
    procedure DoSaveConfiguration(IniFile: TIniFile); override;
  public
    constructor Create(AOwner: TComponent); override;
  published
    property FileName;
    property SkipFirstRows default 0;
    property SheetName: string read FSheetName write SetSheetName;
    property LoadHiddenSheet: boolean read FLoadHiddenSheet
      write SetLoadHiddenSheet default False;
    property NeedFillMerge: Boolean read FNeedFillMerge
      write SetNeedFillMerge default False;
  end;

function GetColIdFromString(Index: string): Integer;
function GetColIdFromColIndex(ColIndex: string): Integer;

{$ENDIF}
{$ENDIF}

implementation

{$IFDEF XLSX}
{$IFDEF VCL6}

uses
  StrUtils;

function GetColIdFromColIndex(ColIndex: string): Integer;
begin
  Result := 0;
  if ColIndex <> '' then
    case Length(ColIndex) of
      1: Result := Ord(ColIndex[1]) - 64;
      2: Result := (Ord(ColIndex[1]) - 64)*26 + (Ord(ColIndex[2]) - 64);
      3: Result := (Ord(ColIndex[1]) - 64)*676 + ((Ord(ColIndex[2]) - 64)*26 + Ord(ColIndex[3]) - 64);
    end;
end;

function GetColIdFromString(Index: string): Integer;
var
  ColValue: string;
  i: Integer;
begin
  Result := 0;
  for i := 1 to Length(Index) do
    if QImport3Common.CharInSet(Index[i],['0'..'9']) then
    begin
      ColValue := Copy(Index, 0, i - 1);
      if ColValue <> '' then
        case Length(ColValue) of
          1: Result := Ord(ColValue[1]) - 64;
          2: Result := (Ord(ColValue[1]) - 64)*26 + (Ord(ColValue[2]) - 64);
          3: Result := (Ord(ColValue[1]) - 64)*676 + ((Ord(ColValue[2]) - 64)*26 + Ord(ColValue[3]) - 64);
        end;
      Break;
    end;
end;

function GetRowIdFromString(Index: string): Integer;
var
  i: Integer;
begin
  Result := 0;
  for i := 1 to Length(Index) do
    if QImport3Common.CharInSet(Index[i], ['0'..'9']) then
    begin
      Result := StrToIntDef(Copy(Index, i, Length(Index)), 0);
      Break;
    end;
end;

{ TXlsxStyleList }

function TXlsxStyleList.GetItem(Index: Integer): TXlsxStyle;
begin
  Result := TXlsxStyle(inherited Items[Index]);
end;

procedure TXlsxStyleList.SetItem(Index: Integer; const Value: TXlsxStyle);
begin
  inherited Items[Index] := Value;
end;

function TXlsxStyleList.Add: TXlsxStyle;
begin
  Result := TXlsxStyle(inherited Add);
end;

{ TXlsxSharedStringList }

function TXlsxSharedStringList.GetItem(Index: Integer): TXlsxSharedStrings;
begin
  Result := TXlsxSharedStrings(inherited Items[Index]);
end;

procedure TXlsxSharedStringList.SetItem(Index: Integer; const Value: TXlsxSharedStrings);
begin
  inherited Items[Index] := Value;
end;

function TXlsxSharedStringList.Add: TXlsxSharedStrings;
begin
  Result := TXlsxSharedStrings(inherited Add);
end;

{ TXlsxMerge }

//procedure TXlsxMerge.SetRange(const Value: string);
procedure TXlsxMerge.SetRange(const Value: qiString);
begin
  if FRange <> Value then
  begin
    FRange := Value;
    FFirstCellName := Copy(Value, 1, Pos(':', Value) - 1);
    FBeginCol := GetColIdFromString(FFirstCellName);
    FBeginRow := GetRowIdFromString(FFirstCellName);
    FEndCol := GetColIdFromString(Copy(Value, Pos(':', Value)+ 1, Length(Value)));
    FEndRow := GetRowIdFromString(Copy(Value, Pos(':', Value)+ 1, Length(Value)));
  end;
end;

{ TXlsxMergeList }

function TXlsxMergeList.GetItem(Index: Integer): TXlsxMerge;
begin
  Result := TXlsxMerge(inherited Items[Index]);
end;

procedure TXlsxMergeList.SetItem(Index: Integer;
  const Value: TXlsxMerge);
begin
  inherited Items[Index] := Value;
end;

function TXlsxMergeList.Add: TXlsxMerge;
begin
  Result := TXlsxMerge(inherited Add);
end;

{ TXlsxCell }

constructor TXlsxCell.Create(Collection: TCollection);
begin
  inherited;
  FCol := 0;
  FRow := 0;
  FName := '';
  FValue := '';
  FIsMerge := False;
  FFormula := '';
  FIsFormulaExist := False;
end;

procedure TXlsxCell.SetFormula(const Value: string);
begin
  if FFormula <> Value then
  begin
    FFormula := Value;
    FIsFormulaExist := True;
  end;
end;

function TXlsxCell.GetName: string;
begin
  if not Self.IsMerge then
    Result := FName
  else
    Result := 'Merge';
end;

procedure TXlsxCell.SetName(const Value: string);
begin
  if not Self.IsMerge then
    FName := Value;
end;

{ TXlsxCellList }

function TXlsxCellList.Add: TXlsxCell;
begin
  Result := TXlsxCell(inherited Add);
end;

function TXlsxCellList.GetItem(Index: Integer): TXlsxCell;
begin
  Result := TXlsxCell(inherited Items[Index]);
end;

procedure TXlsxCellList.SetItem(Index: Integer; const Value: TXlsxCell);
begin
  inherited Items[Index] := Value;
end;

{ TXlsxWorkSheet }

procedure TXlsxWorkSheet.SetSheetID(const Value: integer);
const
  sSheetIDCheck = 'Sheet ID must be > 0!';
begin
  if Value < 1 then
    raise Exception.Create(sSheetIDCheck);

  if FSheetID <> Value then
    FSheetID := Value;
end;

constructor TXlsxWorkSheet.Create(Collection: TCollection);
begin
  inherited;
  FColCount := 0;
  FRowCount := 0;
  FIsHidden := False;
  FCells := TXlsxCellList.Create(TXlsxCell);
  FMergeCells := TXlsxMergeList.Create(TXlsxMerge);
  FDataCells := TqiStringGrid.Create(nil);
end;

destructor TXlsxWorkSheet.Destroy;
begin
  FCells.Free;
  FMergeCells.Free;
  FDataCells.Free;
  inherited;
end;

procedure TXlsxWorkSheet.FillMerge(Cell: TXlsxCell);
var
  i: Integer;
begin
  for i := 0 to FMergeCells.Count - 1 do
    if (Cell.Row in [FMergeCells[i].BeginRow..FMergeCells[i].EndRow]) and
      (Cell.Col in [FMergeCells[i].BeginCol..FMergeCells[i].EndCol]) and
      (not ((Cell.Row = FMergeCells[i].BeginRow) and (Cell.Col = FMergeCells[i].BeginCol)))
    then Cell.Value := FMergeCells[i].Value;
end;

procedure TXlsxWorkSheet.LoadDataCells;
var
  i: Integer;
begin
  FDataCells.ColCount := FColCount;
  FDataCells.RowCount := FRowCount;
  for i := 0 to FCells.Count - 1 do
    FDataCells.Cells[FCells[i].Col - 1, Cells[i].Row - 1] := Cells[i].Value;
end;

{ TXlsxWorkSheetList }

function TXlsxWorkSheetList.GetItems(Index: integer): TXlsxWorkSheet;
begin
  Result := TXlsxWorkSheet(inherited Items[Index]);
end;

procedure TXlsxWorkSheetList.SetItems(Index: integer;
  Value: TXlsxWorkSheet);
begin
  inherited Items[Index] := Value;
end;

function TXlsxWorkSheetList.Add: TXlsxWorkSheet;
begin
  Result := TXlsxWorkSheet(inherited Add);
end;

function TXlsxWorkSheetList.GetFirstSheet: TXlsxWorkSheet;
const
  sSheetCountMustBeMore0 = 'Sheets Count must be > 0!';
begin
  if Self.Count > 0 then
    Result := Items[0]
  else
    raise Exception.Create(sSheetCountMustBeMore0);
end;

function TXlsxWorkSheetList.GetSheetByName(
  Name: qiString): TXlsxWorkSheet;
var
  i: Integer;
begin
  Result := nil;
  if Name = qiString('') then
    Result := GetFirstSheet
  else
    for i := 0 to Self.Count - 1 do
      if UpperCase(Items[i].Name) = UpperCase(Name) then
      begin
        Result := Items[i];
        Break;
      end;
end;

function TXlsxWorkSheetList.GetSheetByID(id: integer): TXlsxWorkSheet;
var
  i: Integer;
begin
  Result := nil;
  for i := 0 to Self.Count - 1 do
    if Items[i].SheetID = id then
    begin
      Result := Items[i];
      Break;
    end;
end;

{ TXlsxWorkbook }

procedure TXlsxWorkbook.LoadSharedStrings;
var
  SharedNode: IXMLDOMNode;
  XmlFileRec: TSearchRec;
  i: Integer;
begin
  try
    if FindFirst(FWorkDir + 'xl\sharedStrings.xml', faAnyFile, XmlFileRec) = 0 then
    begin
      FXMLDoc.load(FWorkDir + 'xl\sharedStrings.xml');
      SharedNode := FXMLdoc.selectSingleNode('/sst');
      for i := 0 to SharedNode.childNodes.length - 1 do
      begin
        FSharedStrings.Add;
        FSharedStrings[i].Text := SharedNode.childNodes[i].text;
      end;
    end;
  finally
    FindClose(XmlFileRec);
  end;
end;

procedure TXlsxWorkbook.LoadStyles;
var
  StyleNode: IXMLDOMNode;
  XmlFileRec: TSearchRec;
  i: Integer;
begin
  try
    if FindFirst(FWorkDir + 'xl\styles.xml', faAnyFile, XmlFileRec) = 0 then
    begin
      FXMLDoc.load(FWorkDir + 'xl\styles.xml');
      StyleNode := FXMLdoc.selectSingleNode('/styleSheet/cellXfs');
      for i := 0 to StyleNode.childNodes.length - 1 do
      begin
        FStyles.Add;
        if Assigned(StyleNode.childNodes[i].attributes) then
          if Assigned(StyleNode.childNodes[i].attributes.getNamedItem('numFmtId')) then
            FStyles[i].NumFmtId :=
              StyleNode.childNodes[i].attributes.getNamedItem('numFmtId').nodeValue;
      end;
    end;
  finally
    FindClose(XmlFileRec);
  end;
end;

function GetSheetID(const r_id: string): integer;
begin
  Result := StrToIntDef(Copy(r_id, 4, Length(r_id) - 3), 0);
end;

procedure TXlsxWorkbook.SetWorkSheets;
var
  SheetNodes: IXMLDOMNodeList;
  XmlFileRec: TSearchRec;
  i: Integer;
  TempWorkSheet: TXlsxWorkSheet;
begin
  try
    if FindFirst(FWorkDir + 'xl\workbook.xml', faAnyFile, XmlFileRec) = 0 then
    begin
      FXMLDoc.load(FWorkDir + 'xl\workbook.xml');
      SheetNodes := FXMLdoc.selectNodes('/workbook/sheets/sheet');
      for i := 0 to SheetNodes.length - 1 do
        if Assigned(SheetNodes[i].attributes.getNamedItem('name')) then
        begin
          if not Assigned(SheetNodes[i].attributes.getNamedItem('state')) then
          begin
            TempWorkSheet := FWorkSheets.Add;
            TempWorkSheet.Name := SheetNodes[i].attributes.getNamedItem('name').nodeValue;
            TempWorkSheet.SheetID := GetSheetID(SheetNodes[i].attributes.getNamedItem('r:id').nodeValue);
          end else
          if (SheetNodes[i].attributes.getNamedItem('state').nodeValue = 'hidden') and
            FLoadHiddenSheets then
            begin
              TempWorkSheet := FWorkSheets.Add;
              TempWorkSheet.Name := SheetNodes[i].attributes.getNamedItem('name').nodeValue;
              TempWorkSheet.IsHidden := True;
            end;
        end;
    end;
  finally
    FindClose(XmlFileRec);
  end;
end;

procedure TXlsxWorkbook.LoadWorkSheets;
var
  Nodes: IXMLDOMNodeList;

  function FindRel(ASheetID: integer): string;
  var
    j: Integer;
  begin
    Result := EmptyStr;
    for j := 0 to Nodes.length - 1 do
      if Assigned(Nodes[j].attributes.getNamedItem('Id'))
      and Assigned(Nodes[j].attributes.getNamedItem('Target')) then
      begin
        if ASheetID = GetSheetID(Nodes[j].attributes.getNamedItem('Id').nodeValue) then
        begin
          Result := StringReplace(
                    Nodes[j].attributes.getNamedItem('Target').nodeValue,
                    '/','\', [rfReplaceAll, rfIgnoreCase]);
          Break;
        end;
      end;
  end;

var
  SRec: TSearchRec;
  i: integer;
  filename: string;
begin
  try
    if FindFirst(FWorkDir + 'xl\_rels\workbook.xml.rels', faDirectory, SRec) = 0 then
      FXMLDoc.load(FWorkDir + 'xl\_rels\workbook.xml.rels');
      Nodes := FXMLdoc.selectNodes('/Relationships/Relationship');
  for i := 0 to FWorkSheets.Count - 1 do
      begin
        filename := FWorkDir + 'xl\' + FindRel(FWorkSheets[i].SheetID);
        LoadSheet( filename, FWorkSheets[i].SheetID);
      end;
  finally
    FindClose(SRec);
  end;
end;

procedure TXlsxWorkbook.SetDataCells;
var
  i: Integer;
begin
  for i := 0 to FWorkSheets.Count - 1 do
    FWorkSheets[i].LoadDataCells;
end;

procedure TXlsxWorkbook.LoadSheet(SheetFile: qiString; Id: integer);
var
  DimNode, FirstMergeNode: IXMLDOMNode;
  CellNodes, MergeNodes: IXMLDOMNodeList;
  CurrentSheet: TXlsxWorkSheet;
  Dimension{, Name}: qiString;
  i, j{, id}, st: Integer;
  TempMerge: TXlsxMerge;
  TempCell: TXlsxCell;


  function XlsxDateTimeFormat(const DateTime: string): TDateTime;
  var
    TempStr: string;
  const
    cXMLDelimiter = '.';
  begin
    TempStr := DateTime;
    if DecimalSeparator <> cXMLDelimiter then
      if Pos(cXMLDelimiter, TempStr) > 0 then
        {$IFDEF VCL6}
        TempStr := AnsiReplaceStr(DateTime, cXMLDelimiter, DecimalSeparator);
        {$ELSE}
        TempStr := StringReplace(DateTime, cXMLDelimiter, DecimalSeparator, [rfReplaceAll, rfIgnoreCase]);
        {$ENDIF}
    Result := StrToFloat(TempStr);
  end;

  {$IFNDEF VCL6}
  function BoolToStr(B: Boolean; UseBoolStrs: Boolean = False): AnsiString;
  const
    cSimpleBoolStrs: array [boolean] of AnsiString = ('0', '-1');
  begin
    if UseBoolStrs then
    begin
      if B then
        Result := 'True'
      else
        Result := 'False';
    end
    else
      Result := cSimpleBoolStrs[B];
  end;
  {$ENDIF}

  function FormatData(s: string; i: Integer): Double;
  begin
    Result := Round(StrToFloat(s)*exp(i*ln(10)))/(exp(i*ln(10)));
  end;

  function GetXlsxPrecentValue(const AValue: string): string;
  var
    snum: string;
    dnum: Double;
    i, digits: Integer;
  begin
    Result := AValue;
    {e.g. format 3.2142857142857099E-3}
    if Pos('E-', AValue) > 0 then
    begin
      snum := Copy(AValue, 1, Pos('E-', AValue) - 1);
      {$IFDEF VCL6}
      dnum := FormatData(AnsiReplaceStr(snum, '.', DecimalSeparator), 2);
      {$ELSE}
      dnum := FormatData(StringReplace(snum, '.', DecimalSeparator), 2);
      {$ENDIF}
      digits := StrToIntDef(Copy(AValue, Pos('E-', AValue) + 2, Length(AValue)), 0) - 2;
      for i := 0 to digits - 1 do
        dnum := dnum / 10;
      Result := FormatFloat('0.00%', dnum);
    end else
    {e.g. format 0.55200000000000005}
    begin
      {$IFDEF VCL6}
      dnum := FormatData(AnsiReplaceStr(AValue, '.', DecimalSeparator), 4) * 100;
      {$ELSE}
      dnum := FormatData(StringReplace(AValue, '.', DecimalSeparator), 4) * 100;
      {$ENDIF}
      Result := FormatFloat('0.00%', dnum);
    end;
  end;

begin
  CurrentSheet := FWorkSheets.GetSheetByID(id);
  if Assigned(CurrentSheet) then
  begin
    FXMLDoc.load(SheetFile);
    DimNode := FXMLdoc.selectSingleNode('/worksheet/dimension');
    Dimension := DimNode.attributes.getNamedItem('ref').nodeValue;

    MergeNodes := FXMLdoc.selectNodes('/worksheet/mergeCells/mergeCell');
    for i := 0 to MergeNodes.length - 1 do
    begin
      TempMerge := CurrentSheet.MergeCells.Add;
      TempMerge.Range := MergeNodes[i].attributes.getNamedItem('ref').nodeValue;
      FirstMergeNode := FXMLdoc.selectSingleNode('/worksheet/sheetData/row/c[@r ="' +
        TempMerge.FirstCellName + '"]/v');
      if Assigned(FirstMergeNode) then
        if Assigned(FirstMergeNode.parentNode.attributes.getNamedItem('t')) then
        begin
          if FirstMergeNode.parentNode.attributes.getNamedItem('t').nodeValue = 's' then
            TempMerge.Value := SharedStrings[StrToInt(FirstMergeNode.text)].Text
          else
          if FirstMergeNode.parentNode.attributes.getNamedItem('t').nodeValue = 'str' then
            TempMerge.Value := FirstMergeNode.text;
        end else
          TempMerge.Value := FirstMergeNode.text;
    end;

    CellNodes := FXMLdoc.selectNodes('/worksheet/sheetData/row/c');
    for i := 0 to CellNodes.length - 1 do
    begin
      TempCell := CurrentSheet.Cells.Add;
      TempCell.Name := CellNodes[i].attributes.getNamedItem('r').nodeValue;
      Dimension := CellNodes[i].attributes.getNamedItem('r').nodeValue;

      TempCell.Col := GetColIdFromString(Dimension);
      if CurrentSheet.ColCount < TempCell.Col then
        CurrentSheet.ColCount := TempCell.Col;
      TempCell.Row := GetRowIdFromString(Dimension);
      if CurrentSheet.RowCount < TempCell.Row then
        CurrentSheet.RowCount := TempCell.Row;

      for j := 0 to CellNodes[i].childNodes.length - 1 do
        if CellNodes[i].childNodes[j].nodeName = 'f' then
          TempCell.Formula :=
            CellNodes[i].childNodes[j].text
        else
        if CellNodes[i].childNodes[j].nodeName = 'v' then
          if Assigned(CellNodes[i].attributes.getNamedItem('t')) then
          begin
            if CellNodes[i].childNodes.item[j].text ='' then TempCell.Value := ''
            else
            if CellNodes[i].attributes.getNamedItem('t').nodeValue = 's' then
              TempCell.Value :=
                SharedStrings[StrToInt(CellNodes[i].childNodes.item[j].text)].Text
            else
            if CellNodes[i].attributes.getNamedItem('t').nodeValue = 'str' then
              TempCell.Value :=
                CellNodes[i].childNodes[j].text
            else
            if CellNodes[i].attributes.getNamedItem('t').nodeValue = 'b' then
              TempCell.Value :=
                BoolToStr(Boolean(StrToIntDef(CellNodes[i].childNodes[j].text, 0)), True);
          end else
          begin
            if Assigned(CellNodes[i].attributes.getNamedItem('s')) then
            begin
              st := CellNodes[i].attributes.getNamedItem('s').nodeValue;
              if Styles.Count > st then
              begin
                case Styles.Items[st].NumFmtId of
                  14, 21, 165..187:
                    if CellNodes[i].childNodes[j].text <> '' then
                      TempCell.Value := DateTimeToStr(XlsxDateTimeFormat(CellNodes[i].childNodes[j].text));
                  10:
                    TempCell.Value := GetXlsxPrecentValue(CellNodes[i].childNodes[j].text);
                else
                    TempCell.Value := CellNodes[i].childNodes[j].text;
                end;
              end else
                TempCell.Value := CellNodes[i].childNodes[j].text;
            end else
              TempCell.Value := CellNodes[i].childNodes[j].text;
          end;
      if NeedFillMerge then
        CurrentSheet.FillMerge(CurrentSheet.Cells[CurrentSheet.Cells.Count - 1]);
    end;
  end;
end;

constructor TXlsxWorkbook.Create;
begin
  inherited;
  FWorkDir := '';
  FLoadHiddenSheets := False;
  FNeedFillMerge := False;
  FXMLDoc := CoDOMDocument.Create;
  FSharedStrings := TXlsxSharedStringList.Create(TXlsxSharedStrings);
  FStyles := TXlsxStyleList.Create(TXlsxStyle);
  FWorkSheets := TXlsxWorkSheetList.Create(TXlsxWorkSheet);
end;

destructor TXlsxWorkbook.Destroy;
begin
  FXMLDoc := nil;
  if Assigned(FWorkSheets) then
    FWorkSheets.Free;
  if Assigned(FStyles) then
    FStyles.Free;
  if Assigned(FSharedStrings) then
    FSharedStrings.Free;
  inherited;
end;

procedure TXlsxWorkbook.Load;
begin
  if FWorkDir <> '' then
  begin
    LoadSharedStrings;
    LoadStyles;
    SetWorkSheets;
    LoadWorkSheets;
    SetDataCells;
  end;
end;

{ TXlsxFile }

constructor TXlsxFile.Create;
begin
  inherited;
  FWorkbook := TXlsxWorkbook.Create;
end;

destructor TXlsxFile.Destroy;
begin
  if Assigned(FWorkbook) then
    FWorkbook.Free;
  inherited;
end;

procedure TXlsxFile.LoadXML(CurrFolder: qiString);
begin
  FWorkbook.CurrFolder := CurrFolder;
  FWorkbook.Load;
end;

{ TQImport3Xlsx }

procedure TQImport3Xlsx.SetSheetName(const Value: string);
begin
  if FSheetName <> Value then
    FSheetName := Value;
end;

procedure TQImport3Xlsx.SetLoadHiddenSheet(const Value: Boolean);
begin
  if FLoadHiddenSheet <> Value then
    FLoadHiddenSheet := Value;
end;

procedure TQImport3Xlsx.SetNeedFillMerge(const Value: Boolean);
begin
  if FNeedFillMerge <> Value then
    FNeedFillMerge := Value;
end;

procedure TQImport3Xlsx.BeforeImport;
begin
  FXlsxFile := TXlsxFile.Create;
  FXlsxFile.Workbook.FLoadHiddenSheets := FLoadHiddenSheet;
  FXlsxFile.Workbook.NeedFillMerge := FNeedFillMerge;
  FXlsxFile.FileName := FileName;
  FXlsxFile.Load;
  if Assigned(FXlsxFile.Workbook.WorkSheets.GetSheetByName(FSheetName))then
    FTotalRecCount := FXlsxFile.Workbook.WorkSheets.GetSheetByName(FSheetName).RowCount;
  inherited;
end;

procedure TQImport3Xlsx.StartImport;
begin
  inherited;
  FCounter := 0;
end;

function TQImport3Xlsx.CheckCondition: Boolean;
begin
  Result := FCounter < FTotalRecCount;
end;

function TQImport3Xlsx.Skip: Boolean;
begin
  Result := (SkipFirstRows > 0) and (FCounter < SkipFirstRows);
end;

procedure TQImport3Xlsx.ChangeCondition;
begin
  Inc(FCounter);
end;

procedure TQImport3Xlsx.FinishImport;
begin
  if not Canceled and not IsCSV then
  begin
    if CommitAfterDone then
      DoNeedCommit
    else if (CommitRecCount > 0) and ((ImportedRecs + ErrorRecs) mod CommitRecCount > 0) then
      DoNeedCommit;
  end;
end;

procedure TQImport3Xlsx.AfterImport;
begin
  FXlsxFile.Free;
  FXlsxFile := nil;
  inherited;
end;

procedure TQImport3Xlsx.FillImportRow;
var
  i, k: Integer;
  strValue: qiString;
  p: Pointer;
  mapValue: qiString;
begin
  FImportRow.ClearValues;
  for i := 0 to FImportRow.Count - 1 do
  begin
    if FImportRow.MapNameIdxHash.Search(FImportRow[i].Name, p) then
    begin
      k := Integer(p);
{$IFDEF VCL7}
      mapValue := Map.ValueFromIndex[k];
{$ELSE}
      mapValue := Map.Values[FImportRow[i].Name];
{$ENDIF}
      strValue := FXlsxFile.Workbook.WorkSheets.GetSheetByName(FSheetName).DataCells.Cells[GetColIdFromColIndex(mapValue) - 1, FCounter];
      FImportRow.SetValue(Map.Names[k], strValue, False);
    end;
    DoUserDataFormat(FImportRow[i]);
  end;
end;

function TQImport3Xlsx.ImportData: TQImportResult;
begin
  Result := qirOk;
  try
    try
      if Canceled  and not CanContinue then
      begin
        Result := qirBreak;
        Exit;
      end;
      DataManipulation;
    except
      on E:Exception do
      begin
        try
          DestinationCancel;
        except
        end;
        DoImportError(E);
        Result := qirContinue;
        Exit;
      end;
    end;
  finally
    if (not IsCSV) and (CommitRecCount > 0) and not CommitAfterDone and
       (
        ((ImportedRecs + ErrorRecs) > 0)
        and ((ImportedRecs + ErrorRecs) mod CommitRecCount = 0)
       )
    then
      DoNeedCommit;
    if (ImportRecCount > 0) and
       ((ImportedRecs + ErrorRecs) mod ImportRecCount = 0) then
      Result := qirBreak;
  end;
end;

procedure TQImport3Xlsx.DoLoadConfiguration(IniFile: TIniFile);
begin
  inherited;
  with IniFile do
  begin
    SkipFirstRows := ReadInteger(XLSX_OPTIONS, XLSX_SKIP_LINES, SkipFirstRows);
    SheetName := ReadString(XLSX_OPTIONS, XLSX_SHEET_NAME, SheetName);
    LoadHiddenSheet := ReadBool(XLSX_OPTIONS, XLSX_LOAD_HIDDEN_SHEET,
      LoadHiddenSheet);
    NeedFillMerge := ReadBool(XLSX_OPTIONS, XLSX_NEED_FILL_MERGE,
      NeedFillMerge);
  end;
end;

procedure TQImport3Xlsx.DoSaveConfiguration(IniFile: TIniFile);
begin
  inherited;
  with IniFile do
  begin
    WriteInteger(XLSX_OPTIONS, XLSX_SKIP_LINES, SkipFirstRows);
    WriteString(XLSX_OPTIONS, XLSX_SHEET_NAME, SheetName);
    WriteBool(XLSX_OPTIONS, XLSX_LOAD_HIDDEN_SHEET, LoadHiddenSheet);
    WriteBool(XLSX_OPTIONS, XLSX_NEED_FILL_MERGE, NeedFillMerge);
  end;
end;

constructor TQImport3Xlsx.Create(AOwner: TComponent);
begin
  inherited;
  SkipFirstRows := 0;
  FLoadHiddenSheet := False;
end;

{$ENDIF}
{$ENDIF}

end.
